<?php
namespace app\stores\controller\admin;
use app\BaseController;
use app\beautiful\model\goods\GoodsOtherSkuDetailModel;
use app\beautiful\model\LevelModel;
use app\beautiful\model\mall\GoodsCategoryModel;
use app\beautiful\model\mall\GoodsModel;
use app\beautiful\model\mall\GoodsSkuModel;
use app\beautiful\model\member\MemberCouponModel;
use app\beautiful\model\member\MemberModel;
use app\beautiful\model\order\GoodsOrderItemModel;
use app\beautiful\model\order\GoodsOrderModel;

use app\beautiful\model\setting\LevelSettingModel;
use app\common\logic\DataLogic;
use app\stores\model\member\MemberLevelModel;
use app\stores\model\order\ChargebackModel;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\facade\Db;


class  Export extends BaseController{
    /**
     * 商品订单/套餐订单--导出
     */
    public function orderExport()

    {


        $order_id = $this->request->param('order_id/d',0);

        $order_type=$this->request->param('order_type');//普通normal拼团pin_tuan秒杀flash_sale预售pre_sale

        $order_sn=$this->request->param('order_sn');

        $goods_name=$this->request->param('goods_name');

        $status=$this->request->param('status/d',0);

        $member_id=$this->request->param('member_id/d',0);

        $mobile=$this->request->param('mobile');

        $type=$this->request->param('type/d',2);//1菜品2产品3套餐
        $time_type = $this->request->param('time_type/d',-1);//0今日1昨日2本周3本月-1全部
        $start_time = $this->request->param('start_time');
        $end_time = $this->request->param('end_time');

        $where=[

            ['shop_id','=',$this->request->shopId],

            ['is_delete','=',0],

        ];
        if($goods_name){
            $order_ids = GoodsOrderItemModel::where(['shop_id'=>$this->request->shopId])->where('goods_name','like','%'.$goods_name.'%')->column('order_id');
            $where[] = ['order_id','In',$order_ids];
        }

        if($type>0){
            $order_ids = GoodsOrderItemModel::where(['shop_id'=>$this->request->shopId,'type'=>$type])->column('order_id');
            $where[] = ['order_id','In',$order_ids];
        }
        switch ($time_type){
            case 0:
                $start_time = date("Y-m-d H:i:s",strtotime("today"));
                $end_time = date("Y-m-d H:i:s",strtotime("today")+86399);
                break;

            case 1:
                $start_time = date('Y-m-d H:i:s',mktime(0,0,0,date('m'),date('d')-1,date('Y')));
                $end_time = date("Y-m-d H:i:s",strtotime("today")-1);
                break;

            case 2:
                $start_time = date("Y-m-d H:i:s",mktime(0, 0 , 0,date("m"),date("d")-date("w")+1,date("Y")));
                $end_time = date("Y-m-d H:i:s",mktime(23,59,59,date("m"),date("d")-date("w")+7,date("Y")));
                break;

            case 3:
                $start_time = date("Y-m-d H:i:s",mktime(0, 0 , 0,date("m"),1,date("Y")));
                $end_time = date("Y-m-d H:i:s",mktime(23,59,59,date("m"),date("t"),date("Y")));

                break;

        }
        if(!empty($start_time) && !empty($end_time)){
            if(!in_array($time_type,[0,1,2,3])){
                $end_time = strtotime($end_time)+86400;
            }else{
                $end_time = strtotime($end_time);
            }
            $where[] = ['add_time','between',[strtotime($start_time),$end_time]];
        }
        if($member_id == 0){

            $member_where=[

                ['shop_id','=',$this->request->shopId]

            ];

            $member_ids= MemberLevelModel::where($member_where)->column('member_id') ?? [];

            $where[]=['member_id','in',$member_ids];

        }else{

            $where[]=['member_id','=',$member_id];

        }
        if($mobile){
            $m_where[]=['mobile','like','%'.$mobile.'%'];

            $member_ids = MemberModel::where($m_where)->column('member_id') ?? [];

            $where[]=['member_id','in',$member_ids];
        }

        if(!empty($status)) $where[]=['status','=',$status];

        if(!empty($order_id)) $where[]=['order_id','=',$order_id];

        if(!empty($order_type)) $where[]=['order_type','=',$order_type];

        if(!empty($order_sn)) $where[]=['order_sn','like','%'.$order_sn.'%'];

        $model=new GoodsOrderModel();

        $order_model=$model->with(['skus','member','partnerCommission']);

        $order=['add_time'=>'desc'];

        $field="order_id,member_id,order_sn,order_type,logistics_type,total_price,need_pay,add_time,pay_time,status,pay_type,order_send_time";

        $append=['add_time_format','pay_time_format','status_means','order_type_means','pay_type_mean'];

        $return = DataLogic::getDataList($order_model,$where,$order,$field,0,0,$append);

        //处理数据
        $lists = $return['list'];
        $order_ids  = [];
        if(empty(!$lists)){
            $key_start = $this->request->limit * ($this->request->page - 1);
            $key_end = $return['total'];

            foreach ($lists as $k => $list){
                $lists[$k]['member_nick_name'] = $list['member']?$list['member']['nick_name']:null;
                $lists[$k]['member_face'] = $list['member']?$list['member']['face']:null;
                $lists[$k]['member_mobile'] = $list['member']?$list['member']['mobile']:null;
                $level_id = getMemberLevel($list['member_id'],$this->request->shopId);
                $level = LevelModel::find($level_id);
                $lists[$k]['level_name'] = $level?$level['name']:null;
                unset($lists[$k]['member']);

                $lists[$k]['show_order_id']= $key_end - ($key_start + $k);

                if($list['order_send_time']) $lists[$k]['order_send_time'] = json_decode($list['order_send_time'],true);
                $commission = 0;
                foreach ($list['partnerCommission'] as $key=>$value){

                    $commission += $value['commission'];
                }
                unset($lists[$k]['partnerCommission']);
                //股东提成
                $lists[$k]['percentage'] = sprintf("%.2f",$commission);
                //商家实收
                $lists[$k]['boss_real_receive'] = sprintf("%.2f",$list['need_pay'] - $list['freight'] - $commission - $list['coupon_money']);

                //数量和优惠
                $return_youhui = $this->youhui($list);
                $lists[$k]['goods_name'] = $return_youhui['goods_name'];
                $lists[$k]['total_num'] = $return_youhui['total_num'];
                $lists[$k]['youhui_name'] = $return_youhui['youhui_name'];
                $lists[$k]['youhui'] = $return_youhui['youhui'];
                $lists[$k]['activity'] = $return_youhui['activity'];
                $lists[$k]['is_bf_refund'] = ($list['refund_money']>0 && $list['status']!=12)?1:0;
                $lists[$k]['need_pay'] = $lists[$k]['need_pay'] - $list['refund_money'];
                //$youhui[$list['order_id']] = $lists[$k]['youhui'];
                //$youhui_name[$list['order_id']] = $lists[$k]['youhui_name'];
                $pay_type_mean[$list['order_id']] = $lists[$k]['pay_type_mean'];
                $status_means[$list['order_id']] = $lists[$k]['status_means'];

            }
            $order_ids = array_column($lists,'order_id');

        }
        if($order_ids){
            $field = 'o.refund_money,o.reason,o.is_settle,o.order_id,o.add_time,o.order_sn,o.total_price,o.need_pay,m.nick_name as member_nick_name,oi.vip_discount_type,
            oi.goods_id,oi.goods_name,oi.order_item_id,oi.category_name,oi.goods_num,oi.goods_price,oi.goods_original_price,oi.discount_detail,oi.buying_price,s.name as supplier_name,sg.is_delete as sg_is_delete';
            $order_item_list = GoodsOrderItemModel::list(['shop_id'=>$this->request->shopId,'order_ids'=>$order_ids],['order_item_id'=>'desc'],$field);
        }else{
            $order_item_list=[];
        }

        //导出数据
        $titleArray = array();
        $titleArray[] = '下单时间';
        $titleArray[] = '订单编号';
        $titleArray[] = '会员';
        $titleArray[] = '总额';
        $titleArray[] = '实付';
        $titleArray[] = '活动品项';
        $titleArray[] = '支付方式';
        $titleArray[] = '订单状态';
        $titleArray[] = '结算状态';
        $titleArray[] = '订单退款';
        $titleArray[] = '退款金额';
        $titleArray[] = '退款备注';
        $titleArray[] = '商品序号';
        $titleArray[] = '分类';
        $titleArray[] = '商品名称';
        $titleArray[] = '供应商';
        $titleArray[] = '订单数量';
        $titleArray[] = '成交数量';
        $titleArray[] = '单位';
        $titleArray[] = '单价';
        $titleArray[] = '成交价';
        $titleArray[] = '优惠';
        $titleArray[] = '小计';
        $titleArray[] = '退单量';
        $titleArray[] = '退单备注';
        $titleArray[] = '成本单价';
        $titleArray[] = '成本小计';
        $filename = '订单数据' . date('YmdHis') . '.csv';

        ob_start();
        header("Expires:-1");
        header("Cache-Control:no_cache");
        header("Pragma:no-cache");
        header("Content-Type: text/csv");
        header("Content-Disposition:filename=" . $filename);
        $fp = fopen('php://output', 'w');

        //转码 防止乱码(比如微信昵称(乱七八糟的))
        fwrite($fp, chr(0xEF) . chr(0xBB) . chr(0xBF));
        fputcsv($fp, $titleArray);

        //汇总数据
       // $tmp[] = '汇总';

        //fputcsv($fp, $tmp);

        $index = 0;
        foreach ($order_item_list as $k=>$row){
            $order_data = GoodsOrderModel::where(['order_id'=>$row['order_id']])->find();
            //退单量
            $chargeback_num = ChargebackModel::where(['shop_id'=>$this->request->shopId,'order_id'=>$row['order_id'],'order_item_id'=>$row['order_item_id'],'is_delete'=>0])->sum('num');
            //最后一次退单备注
            $last_chargeback = ChargebackModel::where(['shop_id'=>$this->request->shopId,'order_id'=>$row['order_id'],'order_item_id'=>$row['order_item_id'],'is_delete'=>0])->order(['chargeback_id'=>'desc'])->find();
            //优惠
            $youhui_info = $youhui_name = '';
            if($row['vip_discount_type']>0){
                //0无优惠1会员折扣2指定价3减价
                if($row['vip_discount_type']==1){
                    $reduce_price = ($row['goods_original_price'] - $row['goods_price']) * $row['goods_num'];
                    $youhui_info = '('.$row['discount_detail'].'折)-'.sprintf("%.2f",$reduce_price);
                    $youhui_name = '会员折扣';
                }else if($row['vip_discount_type']==2){
                    $youhui_info = '-'.sprintf("%.2f",($row['goods_original_price'] - $row['goods_price']) * $row['goods_num']);
                    $youhui_name = '指定价';
                }else{
                    $youhui_info = '-'.sprintf("%.2f",$row['discount_detail']* $row['goods_num']);
                    $youhui_name = '减价';
                }
            }
            $data = [];
            //是否是第一个产品
//            $first = GoodsOrderItemModel::where('order_id',$row['order_id'])->order(['order_item_id'=>'desc'])->find();
//            if($first['order_item_id']==$row['order_item_id']){
//                $refund_info = '无';
//                if($order_data['refund_money']>=$order_data['need_pay']){
//                    $refund_info = '全额退款';
//                }
//                if($order_data['refund_money']>0 && $order_data['refund_money']<$order_data['need_pay']){
//                    $refund_info = '部分退款';
//                }
//                $data[] = $row['add_time_format']."\t";
//                $data[] = $row['order_sn']."\t";
//                $data[] = $row['member_nick_name'];
//                $data[] = $row['total_price'];
//                $data[] = $order_data['status']>1?$order_data['need_pay'] - $order_data['refund_money']:0;
//                $data[] = $youhui_name;
//                $data[] = $pay_type_mean[$row['order_id']];
//                $data[] = $status_means[$row['order_id']];
//                $data[] = $row['is_settle']?'已结算':'未结算';
//                $data[] = $refund_info;
//                $data[] = $order_data['refund_money'];
//                $data[] = $row['reason']??'无';
//            }else{
//                $data[] = '';$data[] = '';$data[] = '';$data[] = '';$data[] = '';$data[] = '';
//                $data[] = '';$data[] = '';$data[] = '';$data[] = '';$data[] = '';$data[] = '';
//            }
            $refund_info = '无';
            if($order_data['refund_money']>=$order_data['need_pay']){
                $refund_info = '全额退款';
            }
            if($order_data['refund_money']>0 && $order_data['refund_money']<$order_data['need_pay']){
                $refund_info = '部分退款';
            }
            $data[] = $row['add_time_format']."\t";
            $data[] = $row['order_sn']."\t";
            $data[] = $row['member_nick_name'];
            $data[] = $row['total_price'];
            $data[] = $order_data['status']>1?$order_data['need_pay'] - $order_data['refund_money']:0;
            $data[] = $youhui_name;
            $data[] = $pay_type_mean[$row['order_id']];
            $data[] = $status_means[$row['order_id']];
            $data[] = $row['is_settle']?'已结算':'未结算';
            $data[] = $refund_info;
            $data[] = $order_data['refund_money'];
            $data[] = $row['reason']??'无';
            $data[] = $row['goods_id'];
            $data[] = $row['category_name'];
            $data[] = $row['goods_name'];
            $data[] = $row['supplier_name'];
            $data[] = $row['goods_num'];
            $data[] = $row['goods_num'] - $chargeback_num;
            $data[] = $row['unit'];
            $data[] = $row['goods_original_price'];
            $data[] = $row['goods_price'];
            $data[] = $youhui_info;
            $data[] = $row['goods_price'] * $row['goods_num'];
            $data[] = $chargeback_num;
            $data[] = $last_chargeback?$last_chargeback['reason']:'无';
            $data[] = $row['buying_price'];
            $data[] = ($row['goods_num'] - $chargeback_num) * $row['buying_price'];
            $index++;
            fputcsv($fp, $data);

            if ($index == 1000) {
                $index = 0;
                ob_flush();
                flush();
            }
        }
        ob_flush();
        flush();
        ob_end_clean();
        exit();
    }

    private function youhui($datas){
        $youhui_name = $goods_name = '';
        $youhui = [];
        $activity = '无';
        //优惠券折扣
        if($datas['member_coupon_id']>0){
            $coupon = MemberCouponModel::with(['couponInfo'])->where(['member_coupon_id'=>$datas['member_coupon_id']])->find();
            if($coupon){
                $coupon = $coupon->toArray();
                $youhui[] = [
                    'discount_name'=>$coupon['couponInfo']['name'],
                    'discount_price'=>$datas['coupon_money']
                ];
                $youhui_name.='/券抵扣';
            }
        }
        //优惠
        if($datas['order_type']=='pin_tuan'){
            $youhui[] = [
                'discount_name'=>'拼团价',
                'discount_price'=>$datas['coupon_money']
            ];
            $youhui_name.='/拼团';
            $activity = '拼团';
        }
        if($datas['order_type']=='flash_sale'){
            $youhui[] = [
                'discount_name'=>'秒杀价',
                'discount_price'=>$datas['coupon_money']
            ];
            $youhui_name.='/秒杀';
            $activity = '秒杀';
        }

        $data = $datas['skus'];
        //数量
        $total_num = 0;
        foreach ($data as $k=>$v){
            if($k==0){
                $goods_name.=$v['goods_name'];
            }
            if($k==1){
                $goods_name.='...';
            }
            $total_num += $v['goods_num'];
            //会员折扣
            if($v['vip_discount_type']>0){
                if($v['vip_discount_type'] == 1){
                    $discount_name = $v['discount_detail'].'折';
                    if($youhui_name=='') $youhui_name='/会员折扣';
                }else if($v['vip_discount_type'] == 2){
                    $discount_name = '指定价';
                }else if($v['vip_discount_type'] == 3){
                    $discount_name = '减价';
                }
                if(empty($youhui)){
                    $youhui[] = [
                        'discount_name'=>$discount_name,
                        'discount_price'=>sprintf("%.2f",$v['goods_original_price']-$v['goods_price'])
                    ];
                }

            }
        }

        $return = [
            'goods_name'=>$goods_name,
            'total_num'=>$total_num,
            'youhui_name'=>$youhui_name!=''?substr($youhui_name,1):'',
            'youhui'=>$youhui,
            'activity'=>$activity
        ];

        return $return;
    }

    /**
     * 商品导出(csv)
     */
    public function goodsExport()

    {
        $category_id = $this->request->param('category_id/d',0);

        $where = [
            ['is_delete','=',0],
            ['shop_id','=',$this->request->shopId],
            ['type','IN',[2]]
        ];

        //查找分类下所有子分类

        $son = GoodsCategoryModel::where(['shop_id'=>$this->request->shopId,'is_delete'=>0,'status'=>1,'pid'=>$category_id])->find();

        if($son){

            $category = GoodsCategoryModel::where(['shop_id'=>$this->request->shopId,'is_delete'=>0,'status'=>1])->select()->toArray();

            $category_ids = $this->get_all_child($category,$category_id);
        }else{
            $category_ids[] =$category_id;
        }

        //获取分类的产品

        $goods_id_arr = [];

        for($i=0;$i<count($category_ids);$i++){

            $goods_ids = GoodsModel::where(['shop_id'=>$this->request->shopId,'is_delete'=>0])->whereRaw("FIND_IN_SET('{$category_ids[$i]}',category_id)")->column('goods_id');

            $goods_id_arr = array_merge($goods_id_arr,$goods_ids);

        }
        
        $where[]=['goods_id','IN',$goods_id_arr];

        $goodsModel = GoodsModel::with(['sku'=>function($query){
            $query->field('quota,sku_id,goods_id,name,original_price,unit_id,gross,discount_detail,crossed_price,start_sales_num,buying_price,sku_info')
                ->where(['shop_id'=>$this->request->shopId,'is_delete'=>0])
                ->json(['discount_detail'],true)
                ->order(['sku_id'=>'asc']);
        },'unit'=>function($query){
            $query->field('other_sku_detail_id,group_detail_name')
                ->where(['shop_id'=>$this->request->shopId,'is_delete'=>0]);
        }])->json(['discount_detail'],true);

        $order = ['sort'=>'desc'];
        $field = "buying_price,goods_id,name,unit_id,original_price,vip_price,crossed_price,gross,discount_detail,is_have_sku,start_sales_num,quota,purchase_information";
        $append = ['add_time_format'];

        $return = DataLogic::getDataList($goodsModel,$where,$order,$field,1,1,$append);

        $lists = $return['list'];

        foreach ($lists as $k=>$v) {
            $goods_category = GoodsCategoryModel::find($v['category_id']);
            //分类名
            $lists[$k]['category_name'] = empty($goods_category) ? '' : $goods_category['name'];

            //规格
            if ($v['sku'] && $v['is_have_sku']) {
                $lists[$k]['sku'] = $this->goodsSku($v['sku']);
            } else {
                $lists[$k]['sku'][] = [
                    'name' => $v['name'],
                    'original_price' => $v['original_price'],
                    'crossed_price' => $v['crossed_price'],
                    'gross' => $v['gross'],
                    'discount_detail' => $v['discount_detail'],
                    'unit_name' => $v['unit']['group_detail_name'],
                    'start_sales_num' => $v['start_sales_num'],
                    'sku_info' => $v['sku_info'],
                    'buying_price' => $v['buying_price'],
                    'quota'=>$v['quota'],
                    'purchase_information'=>$v['purchase_information']
                ];
            }
        }

        //查找当前门店等级
        $level_set = LevelSettingModel::where(['shop_id'=>$this->request->shopId,'is_selected'=>1])->find();
        $level = LevelModel::where(['shop_id'=>$this->request->shopId,'type'=>$level_set['type'],'is_delete'=>0])->order(['level_id'=>'asc'])->select()->toArray();
        //导出数据
        $titleArray = array();
        $titleArray[] = 'ID号';
        $titleArray[] = '门店ID';
        $titleArray[] = '名称';
        $titleArray[] = '多规格';
        $titleArray[] = '多规格ID号';
        $titleArray[] = '产品规格';
        $titleArray[] = '购买须知';
        $titleArray[] = '原价';
        $titleArray[] = '销售价';
        $titleArray[] = '成本价';
        $titleArray[] = '毛利';
        $titleArray[] = '起售量';
        $titleArray[] = '库存';
        $titleArray[] = '会员价';
        if($level_set && $level){
            foreach ($level as $k=>$v){
                $titleArray[] = $v['name'].'指定价';
                $titleArray[] = $v['name'].'减价';
            }
        }
        $filename = '商品数据' . date('YmdHis') . '.csv';

        ob_start();
        header("Expires:-1");
        header("Cache-Control:no_cache");
        header("Pragma:no-cache");
        header("Content-Type: text/csv");
        header("Content-Disposition:filename=" . $filename);
        $fp = fopen('php://output', 'w', 'utf-8');

        //转码 防止乱码(比如微信昵称(乱七八糟的))
        fwrite($fp, chr(0xEF) . chr(0xBB) . chr(0xBF));
        fputcsv($fp, $titleArray);

        $index = $sku_index = 0;
        foreach ($lists as $k=>$row){
            $data = [];
            $data[] = $row['goods_id'];
            $data[] = $row['shop_id'];
            $data[] = $row['name'];
            //会员价
            $discount_type = '无优惠';
            if($row['vip_discount_type']==1){
                $discount_type = '会员折扣';
            }else if($row['vip_discount_type']==2){
                $discount_type = '指定价';
            }else if($row['vip_discount_type']==3){
                $discount_type = '减价';
            }
            $sku = $row['sku'];
            foreach ($sku as $key=>$v) {
                $sku_data = [];
                $huiyuan = $bid_price = $reduce_price = [];
                $discount_detail = $v['discount_detail'];
                if($discount_detail){
                    foreach ($discount_detail as $kk=>$vv){
                        if($row['vip_discount_type']==1){
                            $huiyuan[$vv['level_id']] = $vv['member_price'];
                        }else if($level_set['type']==2){
                            $bid_price[$vv['level_id']] = $vv['bid_price'];
                        }else{
                            $reduce_price[$vv['level_id']] = $vv['reduce_price'];
                        }
                    }
                }
                if($key>0){
                    $sku_data[] = $row['goods_id'];
                    $sku_data[] = $row['shop_id'];
                    $sku_data[] = $row['name'];
                    $sku_data[] = $v['name'];
                    $sku_data[] = $v['sku_id'];
                    $sku_data[] = $v['sku_info'];
                    $sku_data[] = $row['purchase_information'];
                    $sku_data[] = $v['original_price'];
                    $sku_data[] = $v['crossed_price'];
                    $sku_data[] = $v['buying_price'];
                    $sku_data[] = $v['gross'];
                    $sku_data[] = $v['start_sales_num'];
                    $sku_data[] = $v['quota'];
                    $sku_data[] = $discount_type;
                    if($level_set && $level){
                        if($row['vip_discount_type']>0){
                            foreach ($level as $kls=>$vls){
                                $sku_data[] = isset($bid_price[$vls['level_id']])?$bid_price[$vls['level_id']]:'';
                                $sku_data[] = isset($reduce_price[$vls['level_id']])?$reduce_price[$vls['level_id']]:'';
                            }
                        }else{
                            foreach ($level as $kls=>$vls){
                                $sku_data[] = '';
                                $sku_data[] = '';
                            }
                        }

                    }

                    $sku_index++;
                }else{
                    $data[] = count($sku)==1?'':$v['name'];
                    $data[] = count($sku)==1?'':$v['sku_id'];
                    $data[] = $v['sku_info'];
                    $data[] = $row['purchase_information'];
                    $data[] = $v['original_price'];
                    $data[] = $v['crossed_price'];
                    $data[] = $v['buying_price'];
                    $data[] = $v['gross'];
                    $data[] = $v['start_sales_num'];
                    $data[] = $v['quota'];
                    $data[] = $discount_type;
                    if($level_set && $level){
                        if($row['vip_discount_type']>0){
                            foreach ($level as $kls=>$vls){
                                $data[] = isset($bid_price[$vls['level_id']])?$bid_price[$vls['level_id']]:'';
                                $data[] = isset($reduce_price[$vls['level_id']])?$reduce_price[$vls['level_id']]:'';
                            }
                        }else{
                            foreach ($level as $kls=>$vls){
                                $data[] = '';
                                $data[] = '';
                            }
                        }
                    }
                }
                if(isset($sku_data) && $sku_data){

                    fputcsv($fp, $sku_data);

                    if ($sku_index == 1000) {
                        $sku_index = 0;
                        ob_flush();
                        flush();
                    }
                }
            }
            $index++;
            fputcsv($fp, $data);

            if ($index == 1000) {
                $index = 0;
                ob_flush();
                flush();
            }

        }
        ob_flush();
        flush();
        ob_end_clean();
        exit();
    }

    //递归获取所有的子分类的ID
    protected function get_all_child($array,$category_id){
        $arr = array();
        foreach($array as $v){
            if($v['pid'] == $category_id){
                $arr[] = $v['category_id'];
                $arr = array_merge($arr,$this->get_all_child($array,$v['category_id']));
            }
        }
        return $arr;
    }

    protected function goodsSku($sku){
        foreach ($sku as &$v){
            $unit = GoodsOtherSkuDetailModel::where(['shop_id'=>$this->request->shopId,'is_delete'=>0,'other_sku_detail_id'=>$v['unit_id']])->find();
            $v['unit_name'] = $unit?$unit->group_detail_name:null;
        }
        return$sku;
    }

    /**
     * 商品导入(需要判断字段名是否对应，如：门店ID是否为门店id;只修改不新增)
     */
    public function goodsImport(){

        $path = $_FILES['myFile']['tmp_name'].'/'.$_FILES['myFile']['name'];

        $dir = root_path().'/public/upload/';

        $fullname = $dir.$_FILES['myFile']['name'];

        if(file_exists($fullname)){

          //  throw new Exception('文件已经存在');

        }

        move_uploaded_file($_FILES['myFile']['tmp_name'], $fullname);

        $list = getcsvdata($fullname);

        Db::startTrans();

        try{

            foreach ($list as $k=>$v){
                if($k==1){
                    if($v['A']!='ID号') $this->error('A名有误');
                    if($v['B']!='门店ID号') $this->error('B名有误');
                    if($v['C']!='名称') $this->error('C名有误');
                    if($v['D']!='多规格') $this->error('D名有误');
                    if($v['E']!='多规格ID号') $this->error('E名有误');
                    if($v['F']!='产品规格') $this->error('F名有误');
                    if($v['G']!='购买须知') $this->error('G名有误');
                    if($v['H']!='原价') $this->error('H名有误');
                    if($v['I']!='销售价') $this->error('I名有误');
                    if($v['J']!='成本价') $this->error('J名有误');
                    if($v['K']!='毛利') $this->error('K名有误');
                    if($v['L']!='起售量') $this->error('L名有误');
                    if($v['M']!='库存') $this->error('M名有误');
                    if($v['N']!='会员价') $this->error('N名有误');

                    //等级信息
                    $level_num = count($v)-14;
                    //有会员折扣
                    if($level_num>0){
                        //查门店等级
                        $level_set = LevelSettingModel::where(['shop_id'=>$v['B'],'is_selected'=>1])->find();
                        if($level_set && $level_set['type']>0){
                            $level = LevelModel::where(['shop_id'=>$v['B'],'type'=>$level_set['type'],'is_delete'=>0])->order(['level_id'=>'asc'])->select();
                            $level_total_num = count($level);
                            if($level_total_num>5) $this->error('门店等级不能超过5个');
                            if($level_num==($level_total_num*2)){
                                //是否含有会员等级名
                                foreach ($level as $kl=>$vl){
                                    if($kl==0){
                                        $zd = $v['O'];
                                        $jj = $v['P'];
                                    }
                                    if($kl==1){
                                        $zd = $v['Q'];
                                        $jj = $v['R'];
                                    }
                                    if($kl==2){
                                        $zd = $v['S'];
                                        $jj = $v['T'];
                                    }
                                    if($kl==3){
                                        $zd = $v['U'];
                                        $jj = $v['V'];
                                    }
                                    if($kl==4){
                                        $zd = $v['W'];
                                        $jj = $v['X'];
                                    }

                                    $pattern = "/".$vl['name']."/";

                                    if(preg_match($pattern, $zd) && preg_match($pattern, $jj)){

                                    }else{
                                        $this->error($zd.'会员等级与门店不符');
                                    }
                                    exit;
                                }


                            }else{
                                $this->error('会员等级数与门店不符');
                            }
                        }

                    }


                }
                if($k>1){
                    if($v['A'] && $v['B']){
                        //修改
                        $goods_data['name'] = $v['C'];
                        $goods_data['sku_info'] = $v['F'];
                        $goods_data['purchase_information'] = $v['G'];
                        $goods_data['original_price'] = $v['H'];
                        $goods_data['crossed_price'] = $v['I'];
                        $goods_data['buying_price'] = $v['J'];
                        $goods_data['gross'] = $v['K'];
                        $goods_data['start_sales_num'] = $v['L'];
                        $goods_data['quota'] = $v['M'];

                        //等级信息
                        $level_num = count($v)-14;
                        //会员价是否有折扣
                        $vip_discount_type = 0;
                        if($v['N']==='会员折扣'){
                            $vip_discount_type = 1;
                        }
                        if($v['N']==='指定价'){
                            $vip_discount_type = 2;
                        }
                        if($v['N']==='减价'){
                            $vip_discount_type = 3;
                        }
                        if($vip_discount_type<1) $goods_data['vip_discount_type'] = $vip_discount_type;
                        $discount_detail=[];
                        //有会员折扣
                        if(in_array($vip_discount_type,[1,2,3])){
                            if($level_num>0){
                                //查门店等级
                                $level_set = LevelSettingModel::where(['shop_id'=>$v['B'],'is_selected'=>1])->find();
                                if($level_set && $level_set['type']>0){
                                    $level = LevelModel::where(['shop_id'=>$v['B'],'type'=>$level_set['type'],'is_delete'=>0])->order(['level_id'=>'asc'])->select();
                                    $level_total_num = count($level);
                                    if($level_total_num>5) $this->error('门店等级不能超过5个');
                                    if($level_num==($level_total_num*2)){
                                        $discount_detail = [];
                                        //是否含有会员等级名
                                        foreach ($level as $kl=>$vl){
                                            if($kl==0){
                                                $zd = $v['O'];
                                                $jj = $v['P'];
                                            }
                                            if($kl==1){
                                                $zd = $v['Q'];
                                                $jj = $v['R'];
                                            }
                                            if($kl==2){
                                                $zd = $v['S'];
                                                $jj = $v['T'];
                                            }
                                            if($kl==3){
                                                $zd = $v['U'];
                                                $jj = $v['V'];
                                            }
                                            if($kl==4){
                                                $zd = $v['W'];
                                                $jj = $v['X'];
                                            }
                                            $discount_detail[] = [
                                                'name'=>$vl['name'],
                                                'level_id'=>$vl['level_id'],
                                                'bid_price'=>$zd??0,
                                                'member_price'=>$vl['discount_force']??0,
                                                'reduce_price'=>$jj??0,
                                            ];
                                        }

                                    }else{
                                        $this->error('会员等级数与门店不符');
                                    }
                                }

                            }
                        }

                        if($v['D'] && !$v['E']) $this->error($v['D'].'多规格和数据有误');
                        if(!$v['D'] && $v['E']) $this->error($v['E'].'多规格和数据有误');
                        //多规格
                        if($v['D'] && $v['E']){
                            $sku_data['name'] = $v['D'];
                            $sku_data['sku_info'] = $v['F'];
                            $sku_data['purchase_information'] = $v['G'];
                            $sku_data['original_price'] = $v['H'];
                            $sku_data['crossed_price'] = $v['I'];
                            $sku_data['buying_price'] = $v['J'];
                            $sku_data['gross'] = $v['K'];
                            $sku_data['start_sales_num'] = $v['L'];
                            $sku_data['quota'] = $v['M'];
                            if(isset($discount_detail) && $discount_detail) $sku_data['discount_detail'] = json_encode($discount_detail,true);

                            GoodsSkuModel::up($sku_data,['goods_id'=>$v['A'],'sku_id'=>$v['E'],'shop_id'=>$v['B']]);

                            //规格id是否为产品第一个规格
                            $first_sku = GoodsSkuModel::where(['goods_id'=>$v['A'],'shop_id'=>$v['B'],'is_delete'=>0])->order(['sku_id'=>'asc'])->find();

                            if($first_sku && $first_sku['sku_id'] == $v['E']){

                                if(isset($discount_detail) && $discount_detail) $goods_data['discount_detail'] = json_encode($discount_detail,true);
                            }
                            $goods_data['is_have_sku'] = 1;
                        }else{
                            if(isset($discount_detail) && $discount_detail) $goods_data['discount_detail'] = json_encode($discount_detail,true);
                            $goods_data['is_have_sku'] = 0;
                        }

                        $goods_data['vip_discount_type'] = $vip_discount_type;

                        GoodsModel::up($goods_data,['goods_id'=>$v['A'],'shop_id'=>$v['B']]);
                    }

                }


            }

            unlink($fullname);

            Db::commit();

            return $this->success('导入成功');

        }catch(\Exception $e){

            Db::rollback();

            return $this->error($e->getMessage());

        }
    }

    /**
     * 对Spreadsheet方法封装（锐庆）
     * @param array $arr    该数组必须为键值对，键是表格单元，值是单元格的值
     * @param array $data   该数组如果为一维数组，则填写一行，如果为二维数组，则多行数据
     * @param string $name  下载Excel的文件名，可忽略
     * @param string $type  选择文件类型，如果不填写，默认下载为xlsx格式，如果任意填写数值为xls格式
     * @param int  $with 设置sheet默认列宽
     */
    public function downloadExcel(array $arr,array $data,$name="",$type="Xlsx"){

        //文件名处置
        if(empty($name)){
            $name=date("Y-m-d H:i:s")."_".rand(1000,9999);
        }else{
            $name = $name."_".date("Y-m-d H:i:s");
        }

        //内容设置
        $preadsheet = new Spreadsheet();
        $sheet = $preadsheet->getActiveSheet();
        foreach($arr as $k=>$v){
            $sheet->setCellValue($k,$v);
        }
        $sheet->fromArray($data,null,"A2");

        //样式设置
        $sheet->getDefaultColumnDimension()->setWidth(12);

        //设置下载与后缀
        if($type=="Xlsx"){
            header("Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            $suffix = "xlsx";
        }else{
            header("Content-Type:application/vnd.ms-excel");
            $type = "Xls";
            $suffix = "xls";
        }
        header("Content-Disposition:attachment;filename=$name.$suffix");
        header("Cache-Control:max-age=0");//缓存控制
        $writer = IOFactory::createWriter($preadsheet,$type);
        $writer->save("php://output");//数据流
    }
    /**
     * 商品导出(excel)
     */
    public function export()
    {
        $category_id = $this->request->param('category_id/d',0);

        $where = [
            ['is_delete','=',0],
            ['shop_id','=',$this->request->shopId],
            ['type','IN',[2]]
        ];

        //查找分类下所有子分类

        $son = GoodsCategoryModel::where(['shop_id'=>$this->request->shopId,'is_delete'=>0,'status'=>1,'pid'=>$category_id])->find();

        if($son){

            $category = GoodsCategoryModel::where(['shop_id'=>$this->request->shopId,'is_delete'=>0,'status'=>1])->select()->toArray();

            $category_ids = $this->get_all_child($category,$category_id);
        }else{
            $category_ids[] =$category_id;
        }

        //获取分类的产品

        $goods_id_arr = [];

        for($i=0;$i<count($category_ids);$i++){

            $goods_ids = GoodsModel::where(['shop_id'=>$this->request->shopId,'is_delete'=>0])->whereRaw("FIND_IN_SET('{$category_ids[$i]}',category_id)")->column('goods_id');

            $goods_id_arr = array_merge($goods_id_arr,$goods_ids);

        }

        $where[]=['goods_id','IN',$goods_id_arr];
        $goodsModel = GoodsModel::with(['sku'=>function($query){
            $query->field('quota,sku_id,goods_id,name,original_price,unit_id,gross,discount_detail,crossed_price,start_sales_num,buying_price,sku_info')
                ->where(['shop_id'=>$this->request->shopId,'is_delete'=>0])
                ->json(['discount_detail'],true)
                ->order(['sku_id'=>'asc']);
        },'unit'=>function($query){
            $query->field('other_sku_detail_id,group_detail_name')
                ->where(['shop_id'=>$this->request->shopId,'is_delete'=>0]);
        }])->json(['discount_detail'],true);

        $order = ['sort'=>'desc'];
        $field = "buying_price,goods_id,name,unit_id,original_price,vip_price,crossed_price,gross,discount_detail,is_have_sku,start_sales_num,quota,purchase_information";
        $append = ['add_time_format'];

        $return = DataLogic::getDataList($goodsModel,$where,$order,$field,0,0,$append);

        $lists = $return['list'];

        foreach ($lists as $k=>$v) {
            $goods_category = GoodsCategoryModel::find($v['category_id']);
            //分类名
            $lists[$k]['category_name'] = empty($goods_category) ? '' : $goods_category['name'];

            //规格
            if($v['is_have_sku']<1) $v['sku'] = $lists[$k]['sku'] = [];
            if ($v['sku']) {
                $lists[$k]['sku'] = $this->goodsSku($v['sku']);
            } else {
                $lists[$k]['sku'][] = [
                    'name' => $v['name'],
                    'original_price' => $v['original_price'],
                    'crossed_price' => $v['crossed_price'],
                    'gross' => $v['gross'],
                    'discount_detail' => $v['discount_detail'],
                    'unit_name' => $v['unit']['group_detail_name'],
                    'start_sales_num' => $v['start_sales_num'],
                    'sku_info' => $v['sku_info'],
                    'buying_price' => $v['buying_price'],
                    'quota'=>$v['quota'],
                    'purchase_information'=>$v['purchase_information'],
                    'sku_id'=>0
                ];
            }
        }


        //excel数据字段名
        $arr = ["A1"=>"ID号","B1"=>"门店ID","C1"=>"名称","D1"=>"多规格","E1"=>"多规格ID号","F1"=>"产品规格","G1"=>"购买须知","H1"=>"原价","I1"=>"销售价","J1"=>"成本价","K1"=>"毛利","L1"=>"起售量","M1"=>"库存","N1"=>"会员价"];

        //查找当前门店等级
        $level_set = LevelSettingModel::where(['shop_id'=>$this->request->shopId,'is_selected'=>1])->find();
        $level = LevelModel::where(['shop_id'=>$this->request->shopId,'type'=>$level_set['type'],'is_delete'=>0])->order(['level_id'=>'asc'])->select()->toArray();

        if($level_set && $level){
            foreach ($level as $k=>$v){
                if($k==0){
                    $arr2 = ["O1"=>$v["name"]."指定价","P1"=>$v["name"]."减价"];
                }
                if($k==1){
                    $arr2 = ["Q1"=>$v["name"]."指定价","R1"=>$v["name"]."减价"];
                }
                if($k==2){
                    $arr2 = ["S1"=>$v["name"]."指定价","T1"=>$v["name"]."减价"];
                }
                if($k==3){
                    $arr2 = ["U1"=>$v["name"]."指定价","V1"=>$v["name"]."减价"];
                }
                if($k==4){
                    $arr2 = ["W1"=>$v["name"]."指定价","X1"=>$v["name"]."减价"];
                }

                $arr = array_merge($arr,$arr2);
            }
        }
        $export_data = [];
        foreach ($lists as $k=>$row){
            $data = [];
            $data[] = $row['goods_id'];
            $data[] = $row['shop_id'];
            $data[] = $row['name'];
            //会员价
            $discount_type = '无优惠';
            if($row['vip_discount_type']==1){
                $discount_type = '会员折扣';
            }else if($row['vip_discount_type']==2){
                $discount_type = '指定价';
            }else if($row['vip_discount_type']==3){
                $discount_type = '减价';
            }
            $sku = $row['sku'];
            foreach ($sku as $key=>$v) {
                $huiyuan = $bid_price = $reduce_price = [];
                $discount_detail = $v['discount_detail'];
                if($discount_detail){
                    foreach ($discount_detail as $kk=>$vv){
                        if($row['vip_discount_type']==1){
                            $huiyuan[$vv['level_id']] = $vv['member_price'];
                        }else if($level_set['type']==2){
                            $bid_price[$vv['level_id']] = $vv['bid_price'];
                        }else{
                            $reduce_price[$vv['level_id']] = $vv['reduce_price'];
                        }
                    }
                }
                if($key>0){
                    $data = [];
                    $data[] = $row['goods_id'];
                    $data[] = $row['shop_id'];
                    $data[] = $row['name'];
                    $data[] = $v['name'];
                    $data[] = $v['sku_id'];
                    $data[] = $v['sku_info'];
                    $data[] = $row['purchase_information'];
                    $data[] = $v['original_price'];
                    $data[] = $v['crossed_price'];
                    $data[] = $v['buying_price'];
                    $data[] = $v['gross'];
                    $data[] = $v['start_sales_num'];
                    $data[] = $v['quota'];
                    $data[] = $discount_type;
                    if($level_set && $level){
                        if($row['vip_discount_type']>0){
                            foreach ($level as $kls=>$vls){
                                $data[] = isset($bid_price[$vls['level_id']])?$bid_price[$vls['level_id']]:'';
                                $data[] = isset($reduce_price[$vls['level_id']])?$reduce_price[$vls['level_id']]:'';
                            }
                        }else{
                            foreach ($level as $kls=>$vls){
                                $data[] = '';
                                $data[] = '';
                            }
                        }

                    }
                    $export_data[] = $data;
                }else{
                    $data[] = count($sku)==1?'':$v['name'];
                    $data[] = count($sku)==1?'':$v['sku_id'];
                    $data[] = $v['sku_info'];
                    $data[] = $row['purchase_information'];
                    $data[] = $v['original_price'];
                    $data[] = $v['crossed_price'];
                    $data[] = $v['buying_price'];
                    $data[] = $v['gross'];
                    $data[] = $v['start_sales_num'];
                    $data[] = $v['quota'];
                    $data[] = $discount_type;
                    if($level_set && $level){
                        if($row['vip_discount_type']>0){
                            foreach ($level as $kls=>$vls){
                                $data[] = isset($bid_price[$vls['level_id']])?$bid_price[$vls['level_id']]:'';
                                $data[] = isset($reduce_price[$vls['level_id']])?$reduce_price[$vls['level_id']]:'';
                            }
                        }else{
                            foreach ($level as $kls=>$vls){
                                $data[] = '';
                                $data[] = '';
                            }
                        }
                    }
                    $export_data[] = $data;
                }

            }



        }
        $this->downloadExcel($arr,$export_data,"商品数据","xls");
    }
} 